﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Autenticacao
{
    public partial class frm_agendadet : Form
    {
        System.Data.SqlClient.SqlConnection con;

        public Utilizador util4;
        public Cliente cli4;
        public Animal ani4;
        public Exploracao exp4;
        public string op;
        public string dt;
        public string hr;
        public string anie1;


        public frm_agendadet(Utilizador util3, Cliente cli3, Animal ani3, Exploracao exp3, string op, string dt, string hr, string anie)
        {
            this.util4 = util3;
            this.cli4 = cli3;
            this.ani4 = ani3;
            this.exp4 = exp3;
            this.op = op;
            this.dt = dt;
            this.hr = hr;
            this.anie1 = anie;
            InitializeComponent();

            dtt_dia.Text = dt.ToString();

            operador();
        }

        private void cmdagendad_ir_Click(object sender, EventArgs e)
        {
            con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = @"Data Source = ANDRÉ-PC\SQLEXPRESS; Initial Catalog = horsepw_bd; Integrated Security = True;";
            con.Open();

            if (txtagendad_data.Text == "" || txtagendad_hora.Text == "" || txtagendad_nomea.Text == "" || txtagendad_nomec.Text == "")
            {
                MessageBox.Show("Insira todos os dados, por favor!", "Faltam dados!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            else
            {
                if (op == "insanimal")
                {
                    int idv = util4.get_id_vet();
                    int idc = cli4.get_id();
                    int ida = ani4.get_id();
                    SqlCommand command1 = con.CreateCommand();
                    command1.CommandText = "Insert into agenda (data, hora, id_cliente, id_animal, id_veterinario) Values ('" + txtagendad_data.Text + "', '" + txtagendad_hora.Text + "', " + idc + ", " + ida + ", " + idv + ")";
                    command1.CommandType = CommandType.Text;
                    SqlDataReader reader1 = command1.ExecuteReader();

                    MessageBox.Show("Entrada agendada com sucesso!", "Agendamento Efetuado", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    
                    con.Close();
                }
                else
                {
                    if (op == "insaniexp")
                    {
                        int idv = util4.get_id_vet();
                        int idc = cli4.get_id();
                        int ide = exp4.get_id();
                        SqlCommand command2 = con.CreateCommand();
                        command2.CommandText = "Insert into agenda (data, hora, id_cliente, id_exploracao, id_veterinario) Values ('" + txtagendad_data.Text + "', '" + txtagendad_hora.Text + "', " + idc + ", " + ide + ", " + idv + ")";
                        command2.CommandType = CommandType.Text;
                        SqlDataReader reader1 = command2.ExecuteReader();

                        MessageBox.Show("Entrada agendada com sucesso!", "Agendamento Efetuado", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        
                        con.Close();
                    }
                    else
                        MessageBox.Show("Ocorreu um erro! Por favor verifique novamente todos os dados!", "Erro!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            txtagendad_hora.Text = "HH:mm";
            txtagendad_nomea.Text = "";
            txtagendad_nomec.Text = "";
        }

        private void cmdagendad_procdata_Click(object sender, EventArgs e)
        {
            try
            {
                con = new System.Data.SqlClient.SqlConnection();
                con.ConnectionString = @"Data Source = ANDRÉ-PC\SQLEXPRESS; Initial Catalog = horsepw_bd; Integrated Security = True;";
                con.Open();

                DateTime dDate;
                string sdate = null;
                dDate = DateTime.Parse(dtt_dia.Value.ToString());
                sdate = dDate.ToString("yyyy/MM/dd");
                sdate = dDate.ToShortDateString();
                sdate = sdate.Replace("-", "/");

                dDate = DateTime.Parse(sdate);
                sdate = dDate.ToString("yyyy/MM/dd");
                sdate = sdate.Replace("-", "/");

                int id = util4.get_id_vet();
                SqlCommand command1 = con.CreateCommand();
                command1.CommandText = "SELECT data, hora, c.nome, (an.nome) as animal, a.id_veterinario FROM Agenda a INNER JOIN Cliente c ON a.id_cliente = c.id_cliente INNER JOIN Animais an ON a.id_cliente = an.id_cliente WHERE a.data = '" + sdate + "' AND a.id_veterinario = "+ id +" AND a.id_animal IS NOT NULL AND c.oculto = 0 ORDER BY data, hora;";
                SqlDataReader readatrib = command1.ExecuteReader();

                this.dataGridView1.Rows.Clear();

                while (readatrib.Read())
                {
                    int i = this.dataGridView1.Rows.Add();

                    this.dataGridView1[0, i].Value = readatrib["data"].ToString();
                    this.dataGridView1[1, i].Value = readatrib["hora"].ToString();
                    this.dataGridView1[2, i].Value = readatrib["nome"].ToString();
                    this.dataGridView1[3, i].Value = readatrib["animal"].ToString();

                }
                con.Close();

                con = new System.Data.SqlClient.SqlConnection();
                con.ConnectionString = @"Data Source = ANDRÉ-PC\SQLEXPRESS; Initial Catalog = horsepw_bd; Integrated Security = True;";
                con.Open();

                SqlCommand command2 = con.CreateCommand();
                command2.CommandText = "SELECT data, hora, c.nome, (ex.tipo_exploracao) as tipo_exploracao, a.id_veterinario FROM Agenda a INNER JOIN Cliente c ON a.id_cliente = c.id_cliente INNER JOIN Exploracao ex ON a.id_cliente = ex.id_cliente WHERE a.data = '" + sdate + "' AND a.id_veterinario = " + id + " AND a.id_exploracao IS NOT NULL AND c.oculto = 0 ORDER BY data, hora;";
                SqlDataReader readatrib1 = command2.ExecuteReader();

                while (readatrib1.Read())
                {
                    int i = this.dataGridView1.Rows.Add();

                    this.dataGridView1[0, i].Value = readatrib1["data"].ToString();
                    this.dataGridView1[1, i].Value = readatrib1["hora"].ToString();
                    this.dataGridView1[2, i].Value = readatrib1["nome"].ToString();
                    this.dataGridView1[4, i].Value = readatrib1["tipo_exploracao"].ToString();
                }

                this.dataGridView1.Sort(this.dataGridView1.Columns["hora"], ListSortDirection.Ascending);
            }
            catch (Exception er)
            {
                MessageBox.Show(er.ToString(), "Erro!");
            }

            con.Close();
        }

        private void cmdagendad_er_Click(object sender, EventArgs e)
        {
            DateTime dDate;
            string sdate = null;
            dDate = DateTime.Parse(dtt_dia.Value.ToString());
            sdate = dDate.ToString("yyyy/MM/dd");
            sdate = dDate.ToShortDateString();
            sdate = sdate.Replace("-", "/");

            dDate = DateTime.Parse(sdate);
            sdate = dDate.ToString("yyyy/MM/dd");
            sdate = sdate.Replace("-", "/");

            var opr = MessageBox.Show("Tem a certeza que pretende remover a marcação selecionada?", "Confirmar", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            try
            {
                if (opr == DialogResult.Yes)
                {
                    if (this.dataGridView1.RowCount != 0)
                    {
                        int i = this.dataGridView1.CurrentRow.Index;
                        con = new System.Data.SqlClient.SqlConnection();
                        con.ConnectionString = @"Data Source = ANDRÉ-PC\SQLEXPRESS; Initial Catalog = horsepw_bd; Integrated Security = True;";
                        con.Open();

                        string temp_hr = this.dataGridView1.Rows[i].Cells["hora"].Value.ToString();
                        SqlCommand command6 = con.CreateCommand();
                        command6.CommandText = "DELETE FROM Agenda WHERE data = '" + sdate + "' AND hora = '"+ temp_hr +"'";
                        SqlDataReader readatrib6 = command6.ExecuteReader();

                        this.dataGridView1.Rows.Clear();
                        MessageBox.Show("Marcação eliminada com sucesso!", "Eliminar", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                        MessageBox.Show("Primeiro tem de selecionar a marcação!");
                }
            }
            catch (Exception er)
            {
                MessageBox.Show(er.ToString(), "Erro!");
            }

            con.Close();
        }

        private void frm_agendadet_Load(object sender, EventArgs e)
        {

        }

        private void cmdagendad_pnomec_Click(object sender, EventArgs e)
        {
            if (txtagendad_data.Text == "AAAA/MM/DD" || txtagendad_hora.Text == "HH:mm")
            {
                MessageBox.Show("Tem de inserir primeiro a data e a hora!", "Inserir Data e Hora", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            else
            {
                string dt = txtagendad_data.Text.ToString();
                string hr = txtagendad_hora.Text.ToString();
                frm_agendapc frm1 = new frm_agendapc(util4, dt, hr);
                this.Hide();
                frm1.ShowDialog();
            }
        }

        public void enviamail(string nome, string email, string data, string hora, string anex)
        {
            System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();
            message.To.Add(email);
            message.Subject = "Marcação de Consulta veterinária";
            message.From = new System.Net.Mail.MailAddress("ten.horsepower2013@gmail.com");
            message.Body = "Caro cliente: '" + nome + "'" + "\n" + "Tem uma consulta agendada, no seu veterinário habitual, para o dia: '" + data + "' e à hora: '" + hora + "'" + "\n" + "O animal/exploração é: '" + anex + "'";
            System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient("smtp.gmail.com");
            smtp.EnableSsl = true;
            smtp.Credentials = new System.Net.NetworkCredential("ten.horsepower2013@gmail.com", "ten.horsepower");
            smtp.Send(message);
        }

        private void operador()
        {
            if (op == "consultar")
            {
                try
                {
                    con = new System.Data.SqlClient.SqlConnection();
                    con.ConnectionString = @"Data Source = ANDRÉ-PC\SQLEXPRESS; Initial Catalog = horsepw_bd; Integrated Security = True;";
                    con.Open();

                    DateTime dDate;
                    string sdate = null;
                    dDate = DateTime.Parse(dtt_dia.Value.ToString());
                    sdate = dDate.ToString("yyyy/MM/dd");
                    sdate = dDate.ToShortDateString();
                    sdate = sdate.Replace("-", "/");

                    dDate = DateTime.Parse(sdate);
                    sdate = dDate.ToString("yyyy/MM/dd");
                    sdate = sdate.Replace("-", "/");

                    int id = util4.get_id_vet();
                    SqlCommand command1 = con.CreateCommand();
                    command1.CommandText = "SELECT data, hora, c.nome, (an.nome) as animal, a.id_veterinario FROM Agenda a INNER JOIN Cliente c ON a.id_cliente = c.id_cliente INNER JOIN Animais an ON a.id_cliente = an.id_cliente WHERE a.data = '" + sdate + "' AND a.id_veterinario = " + id + " AND a.id_animal IS NOT NULL AND c.oculto = 0 ORDER BY data, hora;";
                    SqlDataReader readatrib = command1.ExecuteReader();

                    this.dataGridView1.Rows.Clear();

                    while (readatrib.Read())
                    {
                        int i = this.dataGridView1.Rows.Add();

                        this.dataGridView1[0, i].Value = readatrib["data"].ToString();
                        this.dataGridView1[1, i].Value = readatrib["hora"].ToString();
                        this.dataGridView1[2, i].Value = readatrib["nome"].ToString();
                        this.dataGridView1[3, i].Value = readatrib["animal"].ToString();

                    }
                    con.Close();

                    con = new System.Data.SqlClient.SqlConnection();
                    con.ConnectionString = @"Data Source = ANDRÉ-PC\SQLEXPRESS; Initial Catalog = horsepw_bd; Integrated Security = True;";
                    con.Open();

                    SqlCommand command2 = con.CreateCommand();
                    command2.CommandText = "SELECT data, hora, c.nome, (ex.tipo_exploracao) as tipo_exploracao, a.id_veterinario FROM Agenda a INNER JOIN Cliente c ON a.id_cliente = c.id_cliente INNER JOIN Exploracao ex ON a.id_cliente = ex.id_cliente WHERE a.data = '" + sdate + "' AND a.id_veterinario = " + id + " AND a.id_exploracao IS NOT NULL AND c.oculto = 0 ORDER BY data, hora;";
                    SqlDataReader readatrib1 = command2.ExecuteReader();

                    while (readatrib1.Read())
                    {
                        int i = this.dataGridView1.Rows.Add();

                        this.dataGridView1[0, i].Value = readatrib1["data"].ToString();
                        this.dataGridView1[1, i].Value = readatrib1["hora"].ToString();
                        this.dataGridView1[2, i].Value = readatrib1["nome"].ToString();
                        this.dataGridView1[4, i].Value = readatrib1["tipo_exploracao"].ToString();
                    }

                    this.dataGridView1.Sort(this.dataGridView1.Columns["hora"], ListSortDirection.Ascending);
                }
                catch (Exception er)
                {
                    MessageBox.Show(er.ToString(), "Erro!");
                }

                con.Close();
            }
            else
            {
                if (op == "inserir")
                {
                    DateTime dDate;
                    string sdate = null;
                    dDate = DateTime.Parse(dtt_dia.Value.ToString());
                    sdate = dDate.ToString("yyyy/MM/dd");
                    sdate = dDate.ToShortDateString();
                    sdate = sdate.Replace("-", "/");

                    dDate = DateTime.Parse(sdate);
                    sdate = dDate.ToString("yyyy/MM/dd");
                    sdate = sdate.Replace("-", "/");

                    txtagendad_data.Text = sdate.ToString();
                }
                else
                {
                    if (op == "voltar")
                    {
                        txtagendad_data.Text = dt.ToString();
                        txtagendad_hora.Text = hr.ToString();
                    }
                    else
                    {
                        if (op == "inscliente")
                        {
                            txtagendad_data.Text = dt.ToString();
                            txtagendad_hora.Text = hr.ToString();
                            txtagendad_nomec.Text = cli4.get_nome().ToString();
                        }
                        else
                        {
                            if (op == "insanimal")
                            {
                                txtagendad_data.Text = dt.ToString();
                                txtagendad_hora.Text = hr.ToString();
                                txtagendad_nomec.Text = cli4.get_nome().ToString();
                                txtagendad_nomea.Text = ani4.get_nome().ToString();
                            }
                            else
                            {
                                if (op == "insaniexp")
                                {
                                    txtagendad_data.Text = dt.ToString();
                                    txtagendad_hora.Text = hr.ToString();
                                    txtagendad_nomec.Text = cli4.get_nome().ToString();
                                    txtagendad_nomea.Text = exp4.get_tipo().ToString();
                                }
                                else
                                    MessageBox.Show("Erro! Ocorreu um erro inesperado!", "Erro!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                            }
                        }
                    }
                }
            }
        }

        private void cmdagendad_pnomea_Click(object sender, EventArgs e)
        {
            if (txtagendad_data.Text == "AAAA/MM/DD" || txtagendad_hora.Text == "HH:mm" || txtagendad_nomec.Text == "")
            {
                MessageBox.Show("Tem de inserir a data, hora e procurar o Cliente", "Inserir data, hora e cliente", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            else
            {
                string dt = txtagendad_data.Text.ToString();
                string hr = txtagendad_hora.Text.ToString();

                if (anie1 == "animal")
                {
                    frm_agendapa frm2 = new frm_agendapa(util4, cli4, dt, hr, anie1);
                    this.Hide();
                    frm2.ShowDialog();
                }
                else
                {
                    if (anie1 == "exploracao")
                    {
                        frmagenda_pe frm3 = new frmagenda_pe(util4, cli4, dt, hr, anie1);
                        this.Hide();
                        frm3.ShowDialog();
                    }
                    else
                    {
                        frm_agendad1 frm4 = new frm_agendad1(util4, cli4, dt, hr, anie1);
                        this.Hide();
                        frm4.ShowDialog();
                    }
                }
            }
        }

        private void cmdagendad_voltar_Click(object sender, EventArgs e)
        {
            frm_agenda frm4 = new frm_agenda(util4);
            this.Hide();
            frm4.ShowDialog();
        }

        private void cmdagendad_enviamail_Click(object sender, EventArgs e)
        {
            if (this.dataGridView1.RowCount != 0)
            {
                string nome;
                string email;
                string data;
                string hora;
                string anex;

                con = new System.Data.SqlClient.SqlConnection();
                con.ConnectionString = @"Data Source = ANDRÉ-PC\SQLEXPRESS; Initial Catalog = horsepw_bd; Integrated Security = True;";
                con.Open();

                DateTime dDate;
                string sdate = null;
                dDate = DateTime.Parse(dtt_dia.Value.ToString());
                sdate = dDate.ToString("yyyy/MM/dd");
                sdate = dDate.ToShortDateString();
                sdate = sdate.Replace("-", "/");

                dDate = DateTime.Parse(sdate);
                sdate = dDate.ToString("yyyy/MM/dd");
                sdate = sdate.Replace("-", "/");

                int id = util4.get_id_vet();
                SqlCommand command1 = con.CreateCommand();
                command1.CommandText = "SELECT data, hora, c.nome, c.email, (an.nome) as animal FROM Agenda a INNER JOIN Cliente c ON a.id_cliente = c.id_cliente INNER JOIN Animais an ON a.id_cliente = an.id_cliente WHERE a.data = '" + sdate + "' AND a.id_veterinario = " + id + " AND a.id_animal IS NOT NULL AND c.oculto = 0 ORDER BY data, hora;";
                SqlDataReader readatrib = command1.ExecuteReader();

                while (readatrib.Read())
                {
                    nome = readatrib["nome"].ToString();
                    email = readatrib["email"].ToString();
                    data = readatrib["data"].ToString();
                    hora = readatrib["hora"].ToString();
                    anex = readatrib["animal"].ToString();
                    enviamail(nome, email, data, hora, anex);
                }
                con.Close();

                con = new System.Data.SqlClient.SqlConnection();
                con.ConnectionString = @"Data Source = ANDRÉ-PC\SQLEXPRESS; Initial Catalog = horsepw_bd; Integrated Security = True;";
                con.Open();

                SqlCommand command2 = con.CreateCommand();
                command2.CommandText = "SELECT data, hora, c.nome, c.email, (ex.tipo_exploracao) as exploracao FROM Agenda a INNER JOIN Cliente c ON a.id_cliente = c.id_cliente INNER JOIN Exploracao ex ON a.id_cliente = ex.id_cliente WHERE a.data = '" + sdate + "' AND a.id_veterinario = " + id + " AND a.id_exploracao IS NOT NULL AND c.oculto = 0 ORDER BY data, hora;";
                SqlDataReader readatrib1 = command2.ExecuteReader();

                while (readatrib1.Read())
                {
                    nome = readatrib1["nome"].ToString();
                    email = readatrib1["email"].ToString();
                    data = readatrib1["data"].ToString();
                    hora = readatrib1["hora"].ToString();
                    anex = readatrib1["exploracao"].ToString();
                    enviamail(nome, email, data, hora, anex);
                }
                con.Close();

                MessageBox.Show("Emails das marcações agendadas enviados com sucesso!", "Emails enviados", MessageBoxButtons.OK, MessageBoxIcon.Information);
            } else
                MessageBox.Show("Pesquise primeiro as marcações diárias para os dias selecionados!", "Informação insuficiente para enviar emails", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
}
